package carassius.DAL;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author siebz0r
 */
public class PlantTable
		extends OrganismTable<PlantRow>
{
	private final String SQL_ADD = "INSERT INTO plant VALUES ( ? , ? )",
			SQL_DELETE = "DELETE FROM plant WHERE id = ?",
			SQL_GET_ALL = "SELECT * FROM organism",
			SQL_GET_BY_ID = "SELECT * FROM plant NATURAL JOIN organism WHERE id = ?",
			SQL_GET_BY_AQUARIUM_ID = "SELECT * FROM plant NATURAL JOIN organism WHERE id in (SELECT organism_id FROM organism_list WHERE aquarium_id = ?)";
	private boolean _allCached;

	//<editor-fold defaultstate="collapsed" desc="Public Methods">
	//<editor-fold defaultstate="collapsed" desc="SQL add">
	public PlantRow add(String scientificName, String alternativeName,
						   String description, Integer minTemperature,
						   Integer maxTemperature, Double maxSize,
						   PictureRow pictureRow, Integer light) throws SQLException
	{
		return add(scientificName, alternativeName, description, minTemperature,
				maxTemperature, maxSize, (pictureRow != null) ? pictureRow.getId() : null, light);
	}

	public PlantRow add(String scientificName, String alternativeName,
						   String description, Integer minTemperature,
						   Integer maxTemperature, Double maxSize,
						   Integer pictureId, Integer light) throws SQLException
	{
		Integer id = add(new Object[]
				{
					scientificName, alternativeName, description, minTemperature,
					maxTemperature, maxSize, pictureId, light
				});
		if (id >= 0)
		{
			return getByPrimaryKey(id);
		}
		return null;
	}

	@Override
	public Integer add(Object[] args) throws SQLException
	{
		int out = -1;
		if (args != null
				&& args.length >= 8
				&& args[7] instanceof Integer)
		{
			synchronized (getRows())
			{
				out = super.add(args);
				if (out >= 0)
				{
					PreparedStatement st = Database.prepareStatement(SQL_ADD);
					st.setInt(1, out);
					st.setInt(2, (Integer) args[7]);

					try
					{
						if (st.executeUpdate() == 1)
						{
							_allCached = false;
							getByPrimaryKey(out);
							_allCached = true;
						}
						else
						{
							out = -1;
						}
					}
					catch (SQLException ex)
					{
						throw ex;
					}
					finally
					{
						st.close();
					}
				}
			}
		}
		return out;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL delete">
	@Override
	public boolean delete(Integer key) throws SQLException
	{
		boolean result = false;
		if (key != null
				&& key >= 0)
		{
			synchronized (getRows())
			{
				PreparedStatement st = Database.prepareStatement(SQL_DELETE);
				st.setInt(1, key);

				try
				{
					st.executeUpdate();
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}

				result = super.delete(key);
			}
		}
		return result;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL get">
	@Override
	public Collection<PlantRow> getAll() throws SQLException
	{
		PreparedStatement st = Database.prepareStatement(SQL_GET_ALL);

		synchronized (getRows())
		{
			if (!_allCached)
			{
				try
				{
					ResultSet query = st.executeQuery();

					while (query.next())
					{
						put(new PlantRow(query));
					}
					_allCached = true;
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
			return getRows().values();
		}
	}

	@Override
	public PlantRow getByPrimaryKey(Integer key) throws SQLException
	{
		PlantRow out = null;
		if (key != null && key >= 0)
		{
			synchronized (getRows())
			{
				out = getRows().get(key);
				if (out != null || _allCached)
				{
					return out;
				}

				PreparedStatement st = Database.prepareStatement(SQL_GET_BY_ID);

				try
				{
					ResultSet query = st.executeQuery();

					if (query.next())
					{
						out = new PlantRow(query);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return out;
	}

	@Override
	public ArrayList<PlantRow> getByAquariumId(int id) throws SQLException
	{
		ArrayList<PlantRow> out = new ArrayList<PlantRow>();
		if (_allCached)
		{
			for (OrganismListRow organismListRow : Database.getOrganismListTable().getByAquariumId(id))
			{
				out.add(getByPrimaryKey(organismListRow.getOrganismId()));
			}
		}
		else
		{
			PreparedStatement st = Database.prepareStatement(SQL_GET_BY_AQUARIUM_ID);
			st.setInt(1, id);
			try
			{
				synchronized (getRows())
				{
					ResultSet query = st.executeQuery();
					while (query.next())
					{
						PlantRow plantRow = new PlantRow(query);
						put(plantRow);
						out.add(plantRow);
					}
				}
			}
			catch (SQLException ex)
			{
				throw ex;
			}
			finally
			{
				st.close();
			}
		}
		return out;
	}
	//</editor-fold>
	//</editor-fold>
}
